from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from service.base_service.logger_service import logger
from service.base_service.database_service import DatabaseService

class DataQueryService:

    def execute_query(self, database, execute_content):
        try:
            session = DatabaseService().create_engine(database)
            with session.connect() as connection:
                # 使用 text(sql) 保持不变，但在实际使用中，应考虑参数化查询以避免SQL注入
                result_proxy = connection.execute(text(execute_content))
                columns = result_proxy.keys()

                # 记录执行的SQL语句，不打印到控制台，而是记录到日志中
                logger.info(f"Executed SQL query: {execute_content}")

                # 处理结果代理（ResultProxy），转换为字典列表
                result = [{key: value for key, value in zip(columns, row)} for row in result_proxy]
                return result
        except SQLAlchemyError as e:
            # 在发生数据库相关错误时记录错误信息
            logger.error(f"An error occurred while executing the SQL query: {e}")
            return []  # 在发生异常时返回空列表，保持函数输出类型一致

    def construct_sql(self, execute_content, execute_variable):

        # 使用text()函数来安全地构造SQL语句
        sql = text(execute_content)

        # 使用bindparams()来绑定参数
        sql = sql.bindparams(**execute_variable)

        sql = str(sql.compile(compile_kwargs={"literal_binds": True}))
        return sql